from ris import db2 #library designed for SQL database connection and querying
from IPython.display import clear_output
import datetime
from datetime import date
import pandas as pd
import os
clear_output()
timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
print 'Notebook run: {}'.format(timestamp)
print os.getcwd()
# %load_ext sql
#Database connections
#gdb = db2.PostgresDb('dotdevpgsql02', 'GISGRID', quiet = True)
cdb = db2.PostgresDb('DOTDEVRHPGSQL01', 'CRASHDATA', quiet = True)
sdb = db2.PostgresDb('dotpgsql01', 'sip', user = cdb.params['user'], db_pass = cdb.params['password'], quiet = True)
#DONE FROM SIP Database
#date.today(today.year, today.month, 1)
#date2 = date(today.year, today.month, 1) - datetime.timedelta(1)
#date2
def mrf_month():
today = date.today()
try:
tm = datetime.datetime(today.year, today.month, today.day+1)
lim= date(today.year, today.month, 1) - datetime.timedelta(1)
except ValueError:
lim= today
return(str(lim))
mrf_month()
#VZV needs project id, name, manager, year, vz status, vz type, unit, unit type, nodeid/segmentid, geom.
#corridors
sip_segs = (db2.query_to_table(sdb, """
SELECT distinct * FROM(
SELECT sp.pid, sp.pjct_name, sp.pm, sp.sip_year, sp.start_date, sp.end_date, spl.description, spg.segmentid, st_setsrid(spg.geom,2263) geom
FROM public.sip_projects sp
join public.sip_projects_geo spg
on sp.pid=spg.pid_fk
join public.sip_lookup spl
on sp.vz_status::varchar = spl.lookupid::varchar
join public.sip_lookup spl2
on sp.unit::varchar = spl2.lookupid::varchar
where sp.status= '15'
and spl.description != 'None'
and spg.nodeid=0
and sip_year > 2013
and sp.end_date<='{}'::date
order by sp.end_date) sip_segs
""".format(mrf_month()))
)
#intersections
sip_nodes = (db2.query_to_table(sdb, """
SELECT distinct * FROM(
SELECT sp.pid, sp.pjct_name, sp.pm, sp.sip_year, sp.start_date, sp.end_date, spl.description, spg.nodeid, st_setsrid(spg.geom,2263) geom
FROM public.sip_projects sp
join public.sip_projects_geo spg
on sp.pid=spg.pid_fk
join public.sip_lookup spl
on sp.vz_status::varchar = spl.lookupid::varchar
join public.sip_lookup spl2
on sp.unit::varchar = spl2.lookupid::varchar
where sp.status= '15'
and spl.description != 'None'
and spg.segmentid=0
and sip_year > 2013
and sp.end_date<='{}'::date
order by sp.end_date) sip_nodes
""".format(mrf_month()))
)
from sqlalchemy import create_engine
def df_to_sql(df,tbl_name,db):
engine = create_engine('postgresql://{user}:{pw}@10.243.154.88:5432/CRASHDATA'.format(user=db.params['user'],
pw=db.params['password']),
echo=False)
df.to_sql(name='{}'.format(tbl_name), con= engine, if_exists = 'replace', index=False)
db.query("""ALTER TABLE {tbl}
ALTER COLUMN geom TYPE Geometry USING geom::Geometry;
grant all on {tbl} to public;""".format(tbl=tbl_name))
return tbl_name
segs = df_to_sql(sip_segs,'sip_segs',cdb)
nodes = df_to_sql(sip_nodes,'sip_nodes',cdb)
from IPython.display import Image
PATH = "C:\Users\soge\Desktop\Jupyter\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\Portal_Projects924.png", width=1000, height=1000)
#cdb.query( """DROP TABLE if exists {segs};
# DROP TABLE if exists {nodes};""".format(segs=segs, nodes=nodes))
sip_corr = (db2.query_to_table(sdb, """
SELECT *
FROM public.sip_projects sp
join public.sip_projects_geo spg
on sp.pid=spg.pid_fk
where sip_year > 2008 and sip_year<2014
and spg.nodeid=0
order by sip_year;""")
)
sip_itx = (db2.query_to_table(sdb, """
SELECT *
FROM public.sip_projects sp
join public.sip_projects_geo spg
on sp.pid=spg.pid_fk
where sip_year > 2008 and sip_year<2014
and spg.segmentid=0
order by sip_year;""")
)
df_to_sql(sip_corr,'sip_corr',cdb)
df_to_sql(sip_itx,'sip_itx',cdb)
# INTERSECTION OVERLAPS
sip_vzv_itx = (db2.query_to_table(cdb,"""
with itx_overlap as (
select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm,
sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ, st_setsrid(vzv.wkb_geometry,2263) vzv_geom
from sip_intersections vzv
join sip_itx sip
on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
where vzv.sip_yr between 2009 and 2013
)
select distinct sip_pid, sip_pjct_name, sip_year, sip_pm,
vzv_sip_id, vzv_pjct_name, vzv_sip_year, vzv_pm, typ --, vzv_geom
from itx_overlap
"""))
#sip_itx_ints.to_csv("Sip_to_VZV_Itx_Overlap-{}.csv".format(datetime.datetime.now().strftime('%Y-%m-%d')),index=False)
# CORRIDOR OVERLAPS
sip_vzv_corr = (db2.query_to_table(cdb,"""
with corr_overlap as (
select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
"SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm,
sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ, sip.geom sip_geom, st_setsrid(vzv."geometry",2263) vzv_geom
from working.sip_corridors vzv
join
(select pid, pjct_name, sip_year, pm, l2.segmentid, l2.geom
from sip_corr sip
join lion l
on sip.segmentid::int = l.segmentid::int
join lion as l2
on l.mft = l2.mft
) sip
on st_dwithin(sip.geom, st_setsrid(vzv."geometry",2263), 10)
where st_length(st_intersection(ST_Buffer(st_setsrid(vzv."geometry",2263), 10),sip.geom))/st_length(sip.geom) > 0.9
and "SIP_YR" between 2009 and 2013
)
select distinct sip_pid, sip_pjct_name, sip_year, sip_pm,
vzv_sip_id, vzv_pjct_name, vzv_sip_year, vzv_pm, typ --,vzv_geom
from corr_overlap
"""))
#sip_vzv_corr.to_csv("Sip_to_VZV_Corr_Overlap-{}.csv".format(datetime.datetime.now().strftime('%Y-%m-%d')),index=False)
# COMPLETE OVERLAP Intersections and Corridors Combined.
CO = (db2.query_to_table(cdb,"""
with itx_overlap as (
select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm,
sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ, st_setsrid(vzv.wkb_geometry,2263) vzv_geom
from sip_intersections vzv
join sip_itx sip
on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
where vzv.sip_yr between 2009 and 2013
)
select * from(
select distinct sip_pid, sip_pjct_name, sip_year, sip_pm,
vzv_sip_id, vzv_pjct_name, vzv_sip_year, vzv_pm, typ --, vzv_geom
from itx_overlap) itxs
union all
select * from (
with corr_overlap as (
select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
"SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm,
sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ, sip.geom sip_geom, st_setsrid(vzv."geometry",2263) vzv_geom
from working.sip_corridors vzv
join
(select pid, pjct_name, sip_year, pm, l2.segmentid, l2.geom
from sip_corr sip
join lion l
on sip.segmentid::int = l.segmentid::int
join lion as l2
on l.mft = l2.mft
) sip
on st_dwithin(sip.geom, st_setsrid(vzv."geometry",2263), 10)
where st_length(st_intersection(ST_Buffer(st_setsrid(vzv."geometry",2263), 10),sip.geom))/st_length(sip.geom) > 0.9
and "SIP_YR" between 2009 and 2013
)
select distinct sip_pid, sip_pjct_name, sip_year, sip_pm,
vzv_sip_id, vzv_pjct_name, vzv_sip_year, vzv_pm, typ --,vzv_geom
from corr_overlap) corrs
"""))
CO.to_csv('SIP_VZV_matches_1007.csv', index = False)
from IPython.display import Image
PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\Itx_overlap_1001.png", width=1000, height=1000)
#from IPython.display import Image
#PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
#Image(filename = PATH + "\Overlap_1001.png", width=1000, height=1000)
#from IPython.display import Image
#PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
#Image(filename = PATH + "\CO_1001.png", width=1000, height=1000)
u_itx_sips = (db2.query_to_table(cdb,"""
with itx_overlap as (
select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm,
sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ
from sip_intersections vzv
join sip_itx sip
on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
where vzv.sip_yr between 2009 and 2013
)
select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,'ITX' typ
from sip_itx sip
where pid not in (select distinct sip_pid from itx_overlap)
"""))
u_itx_sips.to_csv('test1.csv')
#u_itx_sips
u_itx_vzvs = (db2.query_to_table(cdb,"""
with itx_overlap as (
select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,
vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm,
sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ
from sip_intersections vzv
join sip_itx sip
on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
where vzv.sip_yr between 2009 and 2013
)
select distinct vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm, 'ITX' typ
from sip_intersections vzv
where vzv.sip_id not in (select distinct vzv_sip_id from itx_overlap)
and vzv.sip_yr between 2009 and 2013
"""))
#u_itx_vzvs
u_corr_sips = (db2.query_to_table(cdb,"""
with corr_overlap as (
select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year, sip.pm sip_pm,
"SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm,
sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ, sip.geom sip_geom, st_setsrid(vzv."geometry",2263) vzv_geom
from working.sip_corridors vzv
join
(select pid, pjct_name, sip_year, pm, l2.segmentid, l2.geom
from sip_corr sip
join lion l
on sip.segmentid::int = l.segmentid::int
join lion as l2
on l.mft = l2.mft
) sip
on st_dwithin(sip.geom, st_setsrid(vzv."geometry",2263), 10)
where st_length(st_intersection(ST_Buffer(st_setsrid(vzv."geometry",2263), 10),sip.geom))/st_length(sip.geom) > 0.9
and "SIP_YR" between 2009 and 2013
)
select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year, sip.pm sip_pm,'CORR' typ --, sip.geom sip_geom
from sip_corr sip
where sip.pid not in (select distinct sip_pid from corr_overlap)
"""))
#u_corr_sips
unmatched_vzvs = (db2.query_to_table(cdb,"""
with corr_overlap as (
select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year, sip.pm sip_pm,
"SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm,
sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ, sip.geom sip_geom, st_setsrid(vzv."geometry",2263) vzv_geom
from working.sip_corridors vzv
join
(select pid, pjct_name, sip_year, pm, l2.segmentid, l2.geom
from sip_corr sip
join lion l
on sip.segmentid::int = l.segmentid::int
join lion as l2
on l.mft = l2.mft
) sip
on st_dwithin(sip.geom, st_setsrid(vzv."geometry",2263), 10)
where st_length(st_intersection(ST_Buffer(st_setsrid(vzv."geometry",2263), 10),sip.geom))/st_length(sip.geom) > 0.9
and "SIP_YR" between 2009 and 2013
)
select distinct "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 'CORR' typ --, st_setsrid("geometry",2263) vzv_geom
from working.sip_corridors
where "SIP_ID" not in (select distinct vzv_sip_id from corr_overlap)
and "SIP_YR" between 2009 and 2013
"""))
unmatched_vzvs.to_csv('test1.csv')
#unmatched_vzvs